package zy.dao.sell.cashier.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.cashier.CashierDAO;
import zy.entity.sell.cashier.T_Sell_Cashier;
import zy.entity.sell.cashier.T_Sell_Cashier_Set;
import zy.form.StringForm;
import zy.util.CommonUtil;
@Repository
public class CashierDAOImpl extends BaseDaoImpl implements CashierDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object name = params.get("name");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sp_code = params.get("sp_code");//页面传递的店铺编号
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_cashier t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ca_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp_code = :shop_code");
		}
		if(null != name && !"".equals(name)){
        	sql.append(" AND (INSTR(ca_emp_code,:name)>0 OR INSTR(emp_name,:name)>0)");
        }
		if(sp_code != null && !"".equals(sp_code)){
			sql.append(" AND ca_shop_code = :sp_code");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sell_Cashier> list(Map<String, Object> params) {
		Object name = params.get("name");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sp_code = params.get("sp_code");//页面传递的店铺编号
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ca_id,ca_em_code,em_name,ca_shop_code,sp_name AS shop_name,");
		sql.append(" ca_state,ca_erase,ca_maxmoney,ca_minrate,ca_days");
		sql.append(" FROM t_sell_cashier t");
		sql.append(" JOIN t_base_emp e on em_code=ca_em_code and e.companyid=t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = ca_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" AND sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp_code = :shop_code");
		}
		if(null != name && !"".equals(name)){
        	sql.append(" AND (INSTR(ca_em_code,:name)>0 OR INSTR(em_name,:name)>0)");
        }
		if(sp_code != null && !"".equals(sp_code)){
			sql.append(" AND ca_shop_code = :sp_code");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ca_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_Cashier.class));
	}

	@Override
	public T_Sell_Cashier queryByID(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ca_id,ca_em_code,em_name,ca_shop_code,sp_name AS shop_name,");
		sql.append(" ca_state,ca_erase,ca_maxmoney,ca_minrate,ca_days");
		sql.append(" FROM t_sell_cashier t");
		sql.append(" JOIN t_base_emp e ON em_code = ca_em_code AND e.companyid = t.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = ca_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE ca_id=:ca_id");
		try{
			T_Sell_Cashier data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ca_id", id),new BeanPropertyRowMapper<>(T_Sell_Cashier.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void save(T_Sell_Cashier model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sell_cashier(");
		sql.append(" ca_em_code,ca_shop_code,ca_pass,");
		sql.append(" ca_state,ca_maxmoney,ca_minrate,");
		sql.append(" ca_erase,ca_days,companyid");
		sql.append(" ) VALUES (");
		sql.append(" :ca_em_code,:ca_shop_code,:ca_pass,");
		sql.append(" :ca_state,:ca_maxmoney,:ca_minrate,");
		sql.append(" :ca_erase,:ca_days,:companyid");
		sql.append(")");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model),holder);
		model.setCa_id(holder.getKey().intValue());
	}
	@Override
	public void saveLimit(T_Sell_Cashier model) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ss_key cs_key,ss_value cs_value,");
		sql.append(model.getCompanyid() + " as companyid,");
		sql.append("'"+model.getCa_em_code() + "' as cs_em_code");
		sql.append(" FROM common_sellset");
		sql.append(" WHERE ss_type='"+CommonUtil.KEY_CASH+"'");
		
		List<T_Sell_Cashier_Set> list = null;
		list = namedParameterJdbcTemplate.query(sql.toString()
				,new BeanPropertySqlParameterSource(model)
				,new BeanPropertyRowMapper<>(T_Sell_Cashier_Set.class));
		sql.setLength(0);
		sql.append(" INSERT INTO t_sell_cashier_set (");
		sql.append(" cs_key,cs_value,cs_em_code,companyid");
		sql.append(" ) VALUES (");
		sql.append(" :cs_key,:cs_value,:cs_em_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(list.toArray()));
	}

	@Override
	public List<T_Sell_Cashier_Set> limitByCode(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cs_key,cs_value,cs_em_code,companyid");
		sql.append(" FROM t_sell_cashier_set ");
		sql.append(" WHERE cs_em_code=:em_code");
		sql.append(" AND companyid=:companyid");
		try{
			List<T_Sell_Cashier_Set> data = namedParameterJdbcTemplate.query(sql.toString(), param
					, new BeanPropertyRowMapper<>(T_Sell_Cashier_Set.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	@Transactional
	@Override
	public void updateLimit(Map<String, Object> param) {
		@SuppressWarnings("unchecked")
		List<T_Sell_Cashier_Set> setList = (List<T_Sell_Cashier_Set>)param.get("setlist"); 
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sell_cashier_set SET");
		sql.append(" cs_value=:cs_value");
		sql.append(" WHERE 1=1");
		sql.append(" AND cs_key=:cs_key");
		sql.append(" AND cs_em_code=:cs_em_code");
		sql.append(" AND companyid=:companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(setList.toArray()));
	}

	@Override
	public void update(T_Sell_Cashier model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_cashier SET");
		sql.append(" ca_state=:ca_state,ca_maxmoney=:ca_maxmoney,ca_minrate=:ca_minrate,");
		sql.append(" ca_erase=:ca_erase,ca_days=:ca_days");
		sql.append(" WHERE ca_id=:ca_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void updatePwd(T_Sell_Cashier model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_cashier SET");
		sql.append(" ca_pass=:ca_pass ");
		sql.append(" WHERE ca_id=:ca_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void del(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_cashier");
		sql.append(" WHERE ca_id=:ca_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ca_id", id));
	}

	@Override
	public Integer idByCode(T_Sell_Cashier model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ca_id from t_sell_cashier");
		sql.append(" where 1=1");
		if(null != model.getCa_em_code() && !"".equals(model.getCa_em_code())){
			sql.append(" and ca_em_code=:ca_em_code");
		}
		if(null != model.getCa_id() && model.getCa_id() > 0){
			sql.append(" and ca_id <> :ca_id");
		}
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(model) ,Integer.class);
			return id;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<StringForm> listCash(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ca_em_code AS code,em_name AS name");
		sql.append(" FROM t_sell_cashier t");
		sql.append(" JOIN t_base_emp e");
		sql.append(" ON e.em_code=t.ca_em_code");
		sql.append(" AND e.companyid=t.companyid");
		sql.append(" WHERE ca_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(StringForm.class));
	}

}
